package com.jason.core.util;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.util.ResourceUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.*;

/**
 * Created by shijie on 2017/10/30 0030.
 */
public class ExcelOutPutUtil {

    public static void exportExcel(Map<String, Object> params,
                                   HttpServletRequest request,
                                   HttpServletResponse response) {
        try {
            //取得response HttpServletResponse
//            HttpServletResponse response=(HttpServletResponse) params.get("response");
            //取得request HttpServletRequest
//            HttpServletRequest request = (HttpServletRequest) params.get("request");
            //取得文件名
            String fileName = (String) params.get("fileName");

            String absoluteFile = ResourceUtils.getFile("classpath:").getAbsolutePath();
//            absoluteFile = absoluteFile.substring(0, absoluteFile.lastIndexOf("WEB-INF") - 1);
//            String absoluteFile = request.getServletContext().getRealPath("/");
            absoluteFile += "/static/export/";
            File tempFile = new File(absoluteFile);
            if (!tempFile.exists()) {
                tempFile.mkdirs();
            }
            //absoluteFile += new String( fileName.getBytes( "gb2312" ), "ISO8859-1" )+ ".xls";
            absoluteFile += new String((fileName + ".xls").getBytes(), "utf-8");
            tempFile = new File(absoluteFile);
            tempFile.deleteOnExit();
            tempFile.createNewFile();
//            fileName = fileName + ".xls";
           /* try{
                fileName =encodeChineseDownloadFileName(request,fileName);
            }catch(Exception e){
                e.printStackTrace();
            }*/
           /* response.reset();// 清空输出流
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=GBK");
            response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("UTF-8"), "iso-8859-1"));
            response.setHeader("Pragma", "No-cache");
            response.addHeader("Cache-Control", "no-cache");
            OutputStream out = response.getOutputStream();
//            WorkbookSettings settings = new WorkbookSettings ();
//            settings.setWriteAccess(null);
            WritableWorkbook wwb = Workbook.createWorkbook(out); // 建立excel文件*/
            WritableWorkbook wwb = Workbook.createWorkbook(tempFile); // 建立excel文件*/

//            ws.setRowView(0, 800);
            List<Map<String, Object>> tableList = null;
            if (params.get("tableList") != null) {
                tableList = (List<Map<String, Object>>) params.get("tableList");
            }
            List<String> sheetList = null;
            try {
                sheetList = (List<String>) params.get("sheetList");
            } catch (Exception e) {

            }
            for (int t = 0; t < tableList.size(); t++) {
                Map<String, Object> table = tableList.get(t);
                WritableSheet ws = null;
                if (sheetList != null && sheetList.size() > 0) {
                    ws = wwb.createSheet(sheetList.get(t), 10);
                } else {
                    ws = wwb.createSheet("Sheet" + (t + 1), 10);        // 创建一个工作表
                }
                //    设置单元格的文字格式
                WritableFont wf = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false,
                        UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                WritableCellFormat wcf = new WritableCellFormat(wf);
                wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
                wcf.setAlignment(Alignment.CENTRE);

                WritableFont cwf = new WritableFont(WritableFont.ARIAL, 15, WritableFont.NO_BOLD, false,
                        UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                WritableCellFormat wcf2 = new WritableCellFormat(cwf);
                wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
                wcf2.setAlignment(Alignment.CENTRE);

                WritableFont nwf = new WritableFont(WritableFont.ARIAL, 15, WritableFont.NO_BOLD, false,
                        UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                WritableCellFormat wcf1 = new WritableCellFormat(nwf);
                wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);
                wcf1.setAlignment(Alignment.RIGHT);
                List<Map<String, Object>> data = (List<Map<String, Object>>) table.get("data");
                //取得列
                List<List<LinkedHashMap<String, Object>>> cols = (List<List<LinkedHashMap<String, Object>>>) table.get("cols");

                List<LinkedHashMap<String, Object>> colsList = cols.get(0);
                int colSize = colsList.size();
                //列字段名
                String[] colsKeyArray = new String[colSize];
                //列显示名
                String[] colsNameArray = new String[colSize];
                if (cols.size() == 1) {

                    for (int i = 0; i < colsList.size(); i++) {
                        colsKeyArray[i] = String.valueOf(colsList.get(i).get("field"));
                        colsNameArray[i] = String.valueOf(colsList.get(i).get("title"));
                    }

                    //设置表头
                    for (int j = 0; j < colsList.size(); j++) {
                        ws.setColumnView(j, colsNameArray[j].length() * 2 + 12);
                        ws.addCell(new Label(j, 0, colsNameArray[j], wcf));
                    }

                    //设置内容
//                    wcf = new WritableCellFormat();
                    for (int i = 0; i < data.size(); i++) {
                        for (int j = 0; j < colsList.size(); j++) {
                            //ws.setRowView(i, true);
                            Object contentObj = data.get(i).get(colsKeyArray[j]);
                            String content = String.valueOf(contentObj == null ? "" : contentObj);
                            //ws.setColumnView(j, content.length() * 2 + 6);
                            if (NumberUtils.isNumber(content)) {
                                ws.addCell(new Number(j, i + 1, Double.valueOf(content), wcf1));
                            } else {
                                ws.addCell(new Label(j, i + 1, content, wcf2));
                            }
                        }
                    }
                } else {
                    List<Map<String, Object>> titleList = new ArrayList<>();
                    int colsSize = cols.size();
                    //合并行的参照数量，比如共三行，第一行有个合并三行的列，那么给allCols加上1
                    //如果第二行有个合并两行的列，那么给allCols加上1
                    int standRowSpan = colsSize;
                    //计算出所有列
                    Map<String, Object> usingCellMap = new HashMap<>();
                    //只要有rowspan就放入usingCellList
                    List<Map<String, Object>> usingCellList = new ArrayList<>();
                    //两层数组，遍历第一层数组
                    for (int i = 0; i < colsSize; i++) {
                        standRowSpan -= i;
                        int currentCol = 0;
                        List<LinkedHashMap<String, Object>> row = cols.get(i);
                        //遍历第二层数组，下面是title的键值对
                        for (int j = 0; j < row.size(); j++) {
                            //行的下标是i 列的下标是j
                            Map<String, Object> map = row.get(j);
                            String title = String.valueOf(map.get("title"));
                            String field = String.valueOf(map.get("field"));
                            Object colSpan = map.get("colspan");
                            Object rowSpan = map.get("rowspan");
                            int checkResult = checkCellRowspan(currentCol, i, usingCellList);
                            if (checkResult != -1) {
                                currentCol = checkResult;
                            }
                            //如果既有rowspan又有colspan，保存当前坐标以及横纵向跨越格子数(实际上用在保存上一列的数据)
                            if (rowSpan != null || colSpan != null) {
                                if (rowSpan != null && colSpan != null) {
                                    ws.addCell(new Label(currentCol, i, title, wcf));
                                    Integer colSpanNumber = Integer.valueOf(String.valueOf(colSpan));
                                    Integer rowSpanNumber = Integer.valueOf(String.valueOf(rowSpan));
                                    ws.mergeCells(currentCol, i, currentCol + colSpanNumber - 1, i + rowSpanNumber - 1);
                                    if (rowSpanNumber == standRowSpan) {
                                        Map<String, Object> fieldMap = new HashMap<>();
                                        fieldMap.put("field", field);
                                        fieldMap.put("cols", colSpanNumber);
                                        fieldMap.put("col", currentCol);
                                        titleList.add(fieldMap);
                                    }
                                    usingCellMap = new HashMap<>();
                                    usingCellMap.put("col", currentCol);
                                    usingCellMap.put("row", i);
                                    usingCellMap.put("colsNumber", colSpanNumber);
                                    usingCellMap.put("rowsNumber", rowSpanNumber);
                                    usingCellList.add(usingCellMap);
                                }
                                //如果只有rowspan
                                if (rowSpan != null && colSpan == null) {
                                    ws.addCell(new Label(currentCol, i, title, wcf));
                                    Integer rowSpanNumber = Integer.valueOf(String.valueOf(rowSpan));
                                    ws.mergeCells(currentCol, i, currentCol, i + rowSpanNumber - 1);
                                    if (rowSpanNumber == standRowSpan) {
                                        Map<String, Object> fieldMap = new HashMap<>();
                                        fieldMap.put("field", field);
                                        fieldMap.put("cols", 1);
                                        fieldMap.put("col", currentCol);
                                        titleList.add(fieldMap);
                                    }
                                    usingCellMap = new HashMap<>();
                                    usingCellMap.put("col", currentCol);
                                    usingCellMap.put("row", i);
                                    usingCellMap.put("colsNumber", 1);
                                    usingCellMap.put("rowsNumber", rowSpanNumber);
                                    usingCellList.add(usingCellMap);
                                } else if (colSpan != null) {
                                    //如果只有colspan
                                    Integer colSpanNumber = Integer.valueOf(String.valueOf(colSpan));
                                    ws.addCell(new Label(currentCol, i, title, wcf));
                                    ws.mergeCells(currentCol, i, currentCol + colSpanNumber - 1, i);
                                    //只有colspan的时候才直接加上colspannumber
                                    currentCol += colSpanNumber - 1;
                                    Map<String, Object> fieldMap = new HashMap<>();
                                }
                            } else {
                                Map<String, Object> fieldMap = new HashMap<>();
                                fieldMap.put("field", field);
                                fieldMap.put("cols", 1);
                                fieldMap.put("col", currentCol);
                                titleList.add(fieldMap);
                                ws.addCell(new Label(currentCol, i, title, wcf));

                            }
                            currentCol++;
                        }
                    }

                    Collections.sort(titleList, new Comparator<Map<String, Object>>() {
                        public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                            Integer name1 = Integer.valueOf(o1.get("col").toString());//name1是从你list里面拿出来的一个
                            Integer name2 = Integer.valueOf(o2.get("col").toString()); //name1是从你list里面拿出来的第二个name
                            return name1.compareTo(name2);
                        }
                    });


                    //设置内容
//                    wcf = new WritableCellFormat();
                    for (int i = 0; i < data.size(); i++) {
                        for (int j = 0; j < titleList.size(); j++) {

                            String field = String.valueOf(titleList.get(j).get("field"));
                            String content = String.valueOf(data.get(i).get(field));
                            if ("null".equals(content)) {
                                content = "";
                            } else {
                                //ws.setRowView(i, true);
                                int row = i + cols.size();
                                if (NumberUtils.isNumber(content)) {
                                    ws.addCell(new Number(j, row, Double.valueOf(content), wcf1));
                                } else {
                                    ws.addCell(new Label(j, row, content, wcf2));
                                }
                            }
                        }
                    }
                }
            }
            //取得数据
            wwb.write();
            wwb.close();
            //关闭流
//            out.flush();
//            out.close();


//            response.getOutputStream().write(12);
/*            FileInputStream fin = new FileInputStream(outputFile);
            byte[] tempBytes = new byte[2048];
            while (fin.read(tempBytes) != -1) {
                response.getOutputStream().write(tempBytes);
            }
            response.getOutputStream().close();*/
        } catch (IOException e) {
        } catch (RowsExceededException e) {
        } catch (WriteException e) {
        }
    }



    /**
     * 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性
     *
     * @throws Exception
     */
    private static String encodeChineseDownloadFileName(
            HttpServletRequest request, String pFileName) throws Exception {

        String filename = null;
        String agent = request.getHeader("USER-AGENT");
        if (null != agent) {
            if (agent.contains("Firefox")) {//Firefox
                filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";
            } else if (agent.contains("Chrome")) {//Chrome
                filename = new String(pFileName.getBytes(), "ISO8859-1");
            } else {//IE7+
                filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
                filename = filename.replace("+", "%20");
            }
        } else {
            filename = pFileName;
        }
        return filename;
    }

    /**
     * @param col
     * @param row
     * @param usingCellList
     * @return col(应该开始的列坐标)
     */
    private static int checkCellRowspan(int col, int row, List<Map<String, Object>> usingCellList) {
        boolean flag = false;
        for (Map<String, Object> map : usingCellList) {
            //从此处合并的单元格的row和col坐标
            int thisRow = Integer.valueOf(String.valueOf(map.get("row")));
            int thisCol = Integer.valueOf(String.valueOf(map.get("col")));
            //合并行数量
            int rowSpanNumber = Integer.valueOf(String.valueOf(map.get("rowsNumber")));
            //合并列数量
            int colSpanNumber = Integer.valueOf(String.valueOf(map.get("colsNumber")));
            for (int i = 0; i < rowSpanNumber; i++) {
                //遍历每个合并行的坐标
                int currentRow = thisRow + i;
                if (colSpanNumber == 1) {
                    //如果和传入坐标吻合
                    if (row == currentRow && col == thisCol) {
                        //返回col
                        col = col + colSpanNumber;
                        flag = true;
                    }
                } else {
                    //如果存在合并列，并遍历每个合并列的坐标
                    for (int j = 0; j < colSpanNumber; j++) {
                        int currentCol = thisCol + j;
                        if (row == currentRow && col == currentCol) {
                            //返回col
                            col = col + colSpanNumber;
                            flag = true;
                        }
                    }
                }

            }
        }
        if (flag) {
            return col;
        }
        return -1;
    }

    public static void main(String[] args) throws IOException, WriteException {
        File tempFile = new File("f:/output.xls");
        WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
        WritableSheet sheet = workbook.createSheet("TestCreateExcel", 0);
        //1.添加带有字型Formatting的对象
        WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLUE);  //字体
        //2.Format
        WritableCellFormat headerFormat = new WritableCellFormat(headerFont);  //Format 一下
        //3.加入标签  (列,行,"内容",字体)
        Label labelCF = new Label(0, 0, "cell", headerFormat);
        //4.加入工作表
        sheet.addCell(labelCF);
        Label labelCF2 = new Label(1, 0, "cell", headerFormat);
        sheet.addCell(labelCF2);
        sheet.mergeCells(1, 0, 2, 2);
        sheet.mergeCells(1, 1, 2, 2);
//        sheet.setColumnView(1, 20);
        //写入Exel工作表
        workbook.write();
        //关闭Excel工作薄对象
        workbook.close();


        //测试结果：不能重叠合并，那个单元格就是那个单元格 合并后往后推
    }

}
